#!/bin/bash
if [ -n "$2" ]
then
	dateStr=$2
else
	#echo 必须给我传一个日期
	#exit
	dateStr= $(date -d yesterday +%F) 
fi

echo 当前要操作的日期是$dateStr

dim_base_category_info_full="
	--分类维度表	
	insert overwrite table dim_base_category_info_full partition(dt='$dateStr')
	select
		id,
		category_name,
		create_time,
		update_time,
		deleted
	from ods_base_category_info_full
where dt='$dateStr';
"

dim_chapter_full="
	---章节维度表
	insert overwrite table dim_chapter_full partition(dt='$dateStr')
	select
	chapter_id    ,
	chapter_name ,
	video_id     ,
	video_name    ,
	course_id     ,
	course_name   ,
	subject_id,
	subject_name,
	category_id,
	category_name,
	during_sec    ,
	is_free       ,
	create_time   ,
	update_time   ,
	deleted
	from (
			select
					id chapter_id,
					chapter_name,
					course_id,
					video_id,
					create_time,
					update_time,
					is_free,
					deleted
			from ods_chapter_info_full
			where dt = '$dateStr'
		) chapter
	left join
		(
			select
					id e_video_id,
					video_name,
					during_sec
			from ods_video_info_full
			where dt = '$dateStr'
		) video
	on chapter.video_id = video.e_video_id
			left join
		(
			select
					id e_course_id,
					course_name,
					subject_id
			from ods_course_info_full
			where dt='$dateStr'
		) course
	on chapter.course_id = course.e_course_id
	left join
		(
			select
					id e_subject_id,
					subject_name,
					category_id
			from ods_base_subject_info_full
			where dt='$dateStr'
		) subject
	on course.subject_id = subject.e_subject_id
	left join
	(
		select
				id  e_category_id,
				category_name
		from ods_base_category_info_full
		where dt='$dateStr'
	) category
	on subject.category_id = category.e_category_id;
"

dim_course_full="
	--6.课程维度表
	insert overwrite table dim_course_full partition (dt = '$dateStr')
	select t1.id,
		course_name,
		course_slogan,
		course_cover_url,
		subject_id,
		teacher,
		publisher_id,
		chapter_num,
		origin_price,
		reduce_amount,
		actual_price,
		course_introduce,
		create_time,
		update_time,
		deleted,
		comment_txt,
		review_txt,
		review_stars
	from (
         select id,
                course_name,
                course_slogan,
                course_cover_url,
                subject_id,
                teacher,
                publisher_id,
                chapter_num,
                origin_price,
                reduce_amount,
                actual_price,
                course_introduce,
                create_time,
                update_time,
                deleted
         from ods_course_info_full t1
         where dt = '$dateStr'
         ) t1
         left join
     ( --课程评价表
         select data.course_id    course_id,
                data.review_txt   review_txt,
                data.review_stars review_stars
         from ods_review_info_inc
         where dt = '$dateStr'
     ) t2
     on t1.id = t2.course_id
         left join
     ( --章节评价表
         select data.course_id   course_id,
                data.comment_txt comment_txt
         from ods_comment_info_inc
         where dt = '$dateStr'
     ) t3
     on t2.course_id = t3.course_id;
"

dim_paper_full="
	---- 4.试卷维度表 
	with tp as (
		select id,
			paper_title,
			course_id
		from ods_test_paper_full
		where dt = '$dateStr'
	),tpq as (
		select paper_id,
			question_id,
			score
		from ods_test_paper_question_full
		where dt = '$dateStr'
	),te as (
		select paper_id,
			user_id,
			score,
			duration_sec
		from ods_test_exam_full
		where dt = '$dateStr'
	),teq as (
		select data.paper_id,
			data.exam_id,
			data.answer,
			data.is_correct,
			data.score
		from ods_test_exam_question_inc
		where dt = '$dateStr'
	) insert overwrite table dim_paper_full partition (dt = '$dateStr')
	select tp.id,
		tp.paper_title,
		tp.course_id,
		tpq.question_id,
		tpq.score,
		te.user_id,
		te.score,
		te.duration_sec,
		teq.exam_id,
		teq.answer,
		teq.is_correct,
		teq.score
	from tp left join tpq on tp.id = tpq.paper_id
			left join te on tp.id = te.paper_id
			left join teq on tp.id = teq.paper_id;
"

dim_province_full="
	--7省份维度表
	insert overwrite table dim_province_full partition(dt='$dateStr')
		select
			id,
			name,
			region_id,
			area_code,
			iso_code,
			iso_3166_2
		from ods_base_province_full
		where dt='$dateStr';
"

dim_question_full="
	--题目维度表	
	insert overwrite table dim_question_full partition(dt='$dateStr')
	select
	question_id,
		point_id,
		option_txt,
		paper_id,
	question_txt,
		chapter_id,
		create_time,
		update_time,
		question_type,
		publisher_id,
		deleted,
		course_id
	from (
		select
		id ,
		option_txt,
		question_id
		from edu.ods_test_question_option_full
		where dt='$dateStr'
	) tqo
	left join
	(
	select
		id,
		question_txt ,
		chapter_id ,
		create_time  ,
		update_time,
		question_type ,
		publisher_id ,
		deleted ,
		course_id
			from edu.ods_test_question_info_full
			where dt='$dateStr'
			
	
	) tql
	on tql.id=tqo.id
	left join
	(
		select
			id ,
			point_id
		from	edu.ods_test_point_question_full
			where dt='$dateStr'
	
	)	tpq
	on tql.id = tpq.id
	left join
	(
			select
			id   ,
				paper_id
			from edu.ods_test_paper_question_full
			where dt='$dateStr'
	)	paper_question
	on tql.id=	paper_question.id;
"

dim_subject_full="
--5.学科维度表
	insert overwrite table dim_subject_full partition(dt='$dateStr')
	select
		subject.id,subject_name,category_id,
		subject.create_time,subject.update_time,
		subject.deleted
	from
	(
		select
			id,
			subject_name,
			category_id,
			create_time,
			update_time,
			deleted
		from ods_base_subject_info_full
		where dt='$dateStr'
	)subject
	left join
	(
		select
			id,
			category_name,
			create_time,
			update_time,
			deleted
		from ods_base_category_info_full
		where dt='$dateStr'
	)category
	on subject.category_id= category.id;
"

dim_user_zip="
    set hive.exec.dynamic.partition.mode=nonstrict;
    with old as (
        select id,
               login_name,
               nick_name,
               real_name,
               phone_num,
               email,
               user_level,
               birthday,
               gender,
               create_time,
               operate_time,
               start_date,
               end_date
        from dim_user_zip
        where dt = '9999-12-31'
    ),new as (
        select id,
               login_name,
               nick_name,
               real_name,
               phone_num,
               email,
               user_level,
               birthday,
               gender,
               create_time,
               operate_time,
               start_date,
               end_date
        from (
                 select data.id id,
                        data.login_name   login_name,
                        data.nick_name    nick_name,
                        data.real_name    real_name,
                        data.phone_num    phone_num,
                        data.email        email,
                        data.user_level   user_level,
                        data.birthday     birthday,
                        data.gender       gender,
                        data.create_time  create_time,
                        data.operate_time operate_time,
                        '$dateStr'  start_date,
                        '9999-12-31'  end_date,
                        row_number() over (partition by data.id order by ts desc ) rn
                 from ods_user_info_inc
                 where dt = '$dateStr'
             ) t1
        where t1.rn = 1
    ),full_user as (
        select new.id new_id,
               new.login_name   new_login_name,
               new.nick_name    new_nick_name,
               new.real_name    new_real_name,
               new.phone_num    new_phone_num,
               new.email        new_email,
               new.user_level   new_user_level,
               new.birthday     new_birthday,
               new.gender       new_gender,
               new.create_time  new_create_time,
               new.operate_time new_operate_time,
               new.start_date   new_start_date,
               new.end_date     new_end_date,
               old.id           old_id,
               old.login_name   old_login_name,
               old.nick_name    old_nick_name,
               old.real_name    old_real_name,
               old.phone_num    old_phone_num,
               old.email        old_email,
               old.user_level   old_user_level,
               old.birthday     old_birthday,
               old.gender       old_gender,
               old.create_time  old_create_time,
               old.operate_time old_operate_time,
               old.start_date   old_start_date,
               old.end_date     old_end_date
        from new full join old
                           on new.id = old.id
    )
    insert overwrite table dim_user_zip partition (dt)
    --最新数据
    select if(new_id is not null,new_id,old_id),
           if(new_login_name is not null,new_login_name,old_login_name),
           if(new_nick_name is not null,new_nick_name,old_nick_name),
           if(new_real_name is not null,new_real_name,old_real_name),
           if(new_phone_num is not null,new_phone_num,old_phone_num),
           if(new_email is not null,new_email,old_email),
           if(new_user_level is not null,new_user_level,old_user_level),
           if(new_birthday is not null,new_birthday,old_birthday),
           if(new_gender is not null,new_gender,old_gender),
           if(new_create_time is not null,new_create_time,old_create_time),
           if(new_operate_time is not null,new_operate_time,old_operate_time),
           if(new_start_date is not null,new_start_date,old_start_date),
           if(new_end_date is not null, new_end_date,old_end_date),
           if(new_end_date is not null, new_end_date,old_end_date)
    from full_user
    where new_id is not null or (old_id is not null and new_id is null)
    union
    --是失效数据
    select old_id,
           old_login_name,
           old_nick_name,
           old_real_name,
           old_phone_num,
           old_email,
           old_user_level,
           old_birthday,
           old_gender,
           old_create_time,
           old_operate_time,
           old_start_date,
           cast(date_sub('$dateStr',1) as string),     --结束时间 = 当前时间 - 1
           cast(date_sub('$dateStr',1) as string)
    from full_user
    where old_id is not null and new_id is not null;
"

case $1 in
"dim_base_category_info_full")
	hive --database edu -e "$dim_base_category_info_full"
	;;
"dim_chapter_full")
	hive --database edu -e "$dim_chapter_full"
	;;
"dim_course_full")
	hive --database edu -e "$dim_course_full"
	;;
"dim_paper_full")
	hive --database edu -e "$dim_paper_full"
	;;
"dim_province_full")
	hive --database edu -e "$dim_province_full"
	;;
"dim_question_full")
	hive --database edu -e "$dim_question_full"
	;;
"dim_subject_full")
	hive --database edu -e "$dim_subject_full"
	;;
"dim_user_zip")
	hive --database edu -e "$dim_user_zip"
	;;
"all")
	hive --database edu -e "$dim_user_zip$dim_base_category_info_full$dim_chapter_full$dim_course_full$dim_paper_full$dim_province_full$dim_question_full$dim_subject_full"
	;;
esac








